﻿using DotNetCommon.Accessors;
using DotNetCommon.Data;
using DotNetCommon.Extensions;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Text.Json.Nodes;

namespace DBUtil;

public partial class DBAccess
{
    #region ConvertToSqlSeg ParseJsonDataType ConvertJsonLiteralToSql ConvertJsonVariableToSql
    private static readonly List<Type> numberTypes =
    [
        typeof(sbyte),
        typeof(byte),
        typeof(short),
        typeof(ushort),
        typeof(int),
        typeof(uint),
        typeof(long),
        typeof(ulong),
        typeof(float),
        typeof(double),
        typeof(decimal)
    ];
    /// <summary>
    /// 将任意对象转为可拼接的sql,示例:
    /// <list type="bullet">
    /// <item>字符串: db.ConvertToSqlSeg("刘备") => '刘备'</item>
    /// <item>原始字符串: db.ConvertToSqlSeg(new RawString("user()")) => user()</item>
    /// <item>日期: db.ConvertToSqlSeg(DateTime.Now) => '2023-01-19 01:02:03.123'</item>
    /// <item>日期带格式: db.ConvertToSqlSeg(DateTime.Now,"yyyy-MM-dd") => '2023-01-19'</item>
    /// <item>日期带时区: sqlserver: db.ConvertToSqlSeg(DateTimeOffset.Now) => '2023-01-19 01:02:03.1234567 +08:00'</item>
    /// <item>日期带时区: mysql: db.ConvertToSqlSeg(DateTimeOffset.Now) => '2023-01-19 01:02:03.1234567+08:00'</item>
    /// <item>GUID: db.ConvertToSqlSeg(Guid.NewGuid()) => '10adf41f2e974a289025b5355d473eca'</item>
    /// <item>GUID带格式: db.ConvertToSqlSeg(Guid.NewGuid(),"D") => '10adf41f-2e97-4a28-9025-b5355d473eca'</item>
    /// <item>字节数组: db.ConvertToSqlSeg(new byte[] { 0x12, 0x13 }) => 0x1213</item>
    /// <item>数组集合: db.ConvertToSqlSeg(new[] { 1, 2, 3 }) => (1,2,3)</item>
    /// <item>元祖: db.ConvertToSqlSeg((1, 2)) => (1,2)</item>
    /// </list>
    /// 支持的参数形式:
    /// <list type="number">
    /// <item>guid: "N/D/B/P/X"</item>
    /// <item>enum: true/false 表示是否将枚举转为字符串</item>
    /// <item>datetime: "yyyy-MM-dd HH:mm:ss.fff"</item>
    /// </list>
    /// </summary>
    /// <remarks>注意: guid默认转换为 "10adf41f2e974a289025b5355d473eca" 如果想中间带下划线,使用: db.ConvertToSqlSeg(guid,"D") => "10adf41f-2e97-4a28-9025-b5355d473eca" </remarks>
    public virtual Result<string> ConvertToSqlSeg(object obj, object args = null)
    {
        //简单类型
        if (obj == null || obj is DBNull) return Result.Ok("null");
        if (obj is string str) return Result.Ok($"{ProtectStringToSeg(str)}");
        if (obj is char c) return Result.Ok($"{ProtectStringToSeg(c + "")}");
        if (obj is RawString rawStr) return Result.Ok(rawStr.String);
        if (obj is bool b) return Result.Ok(b ? "1" : "0");

        //数字
        var type = obj.GetType();
        if (numberTypes.Contains(type)) return Result.Ok(obj.ToString());

        //枚举类型
        if (type.IsEnum)
        {
            //将枚举转为字符串
            if (args is bool flag && flag) return Result.Ok($"'{obj}'");
            return Result.Ok(obj.To<int>().ToString());
        }

        //guid
        if (obj is Guid guid)
        {
            if (args is string s && s.IsNotNullOrWhiteSpace()) return Result.Ok($"'{guid.ToString(s)}'");
            return Result.Ok($"'{guid:N}'");
        }

        //datetime
        //sqlserver 的 datetime 小数位数3,超过将报错 而不是截断 datetime2 会自动截断
        //mysql 最多支持6位小数 超出的自动截断
        if (obj is DateTime dateTime)
        {
            string seg = null;
            if (args is string s) seg = dateTime.ToString(s);
            else seg = dateTime.ToCommonStampString();
            seg = seg.TrimEnd('0').TrimEnd('.');
            if (seg.EndsWith(" 00:00:00")) seg = seg[..^" 00:00:00".Length];
            return Result.Ok($"'{seg}'");
        }

        //datetimeoffset sqlserver 的 datetimeoffset 最大精度7位,超过会自动截断
        if (obj is DateTimeOffset dateTimeOffset)
        {
            var seg = dateTimeOffset.ToString("yyyy-MM-dd HH:mm:ss.fffffff").TrimEnd('0').Trim('.');
            if (seg.EndsWith(" 00:00:00")) seg = seg[..^" 00:00:00".Length];
            return Result.Ok($"'{seg} {dateTimeOffset:zzz}'");
        }

        //timespan
        if (obj is TimeSpan timeSpan) return Result.Ok($"'{timeSpan}'");
        if (obj is DateOnly dateOnly) return Result.Ok($"'{dateOnly:yyyy-MM-dd}'");
        if (obj is TimeOnly timeOnly) return Result.Ok($"'{timeOnly:HH:mm:ss.fff}'");

        #region 二进制
        if (obj is IEnumerable<byte> bytes)
        {
            var sb = new StringBuilder();
            sb.Append("0x");
            foreach (var by in bytes) sb.Append(by.ToString("X2"));
            return Result.Ok(sb.ToString());
        }
        if (obj is IEnumerable<sbyte> sbytes)
        {
            var sb = new StringBuilder();
            sb.Append("0x");
            foreach (var by in sbytes) sb.Append(by.ToString("X2"));
            return Result.Ok(sb.ToString());
        }
        #endregion

        #region 数字集合或数组
        //先处理枚举数组 因为它可能被识别成 IEnumerable<int>
        if (args is true && obj is IEnumerable _arr)
        {
            Type _type = null;
            foreach (var item in _arr)
            {
                _type = item?.GetType();
                if (_type != null) break;
            }
            if (_type != null && (_type.IsEnum || Nullable.GetUnderlyingType(_type)?.IsEnum == true))
            {
                var _sb = new StringBuilder();
                _sb.Append('(');
                var idx = 0;
                foreach (var item in _arr)
                {
                    if (idx > 0) _sb.Append(',');
                    _sb.Append($"'{item.ToString()}'");
                    idx++;
                }
                _sb.Append(')');
                return Result.Ok(_sb.ToString());
            }
        }
        if (obj is IEnumerable<short> li_short) return Result.Ok($"({li_short.ToStringSeparated(",")})");
        if (obj is IEnumerable<ushort> li_ushort) return Result.Ok($"({li_ushort.ToStringSeparated(",")})");
        if (obj is IEnumerable<int> li_int) return Result.Ok($"({li_int.ToStringSeparated(",")})");
        if (obj is IEnumerable<uint> li_uint) return Result.Ok($"({li_uint.ToStringSeparated(",")})");
        if (obj is IEnumerable<long> li_long) return Result.Ok($"({li_long.ToStringSeparated(",")})");
        if (obj is IEnumerable<ulong> li_ulong) return Result.Ok($"({li_ulong.ToStringSeparated(",")})");
        if (obj is IEnumerable<float> li_float) return Result.Ok($"({li_float.ToStringSeparated(",")})");
        if (obj is IEnumerable<double> li_double) return Result.Ok($"({li_double.ToStringSeparated(",")})");
        if (obj is IEnumerable<decimal> li_decimal) return Result.Ok($"({li_decimal.ToStringSeparated(",")})");
        if (obj is IEnumerable<string> li_string) return Result.Ok($"({li_string.Select(i => $"{ProtectStringToSeg(i)}").ToStringSeparated(",")})");
        if (obj is IEnumerable<object> li_object) return Result.Ok($"({li_object.Select(i => ConvertToSqlSeg(i, args).UnWrap()).ToStringSeparated(",")})");
        //插入一个json列 insertDic("ext",dic)
        if (obj is IDictionary dic) return Result.Ok(ProtectStringToSeg(dic.ToJson()));
        if (obj is IEnumerable li_object2)
        {
            var li = new List<string>();
            foreach (var item in li_object2) li.Add(ConvertToSqlSeg(item, args).UnWrap());
            return Result.Ok($"({li.ToStringSeparated(",")})");
        }
        #endregion

        #region 元组
        if (obj is ValueTuple<int, int> valueTuple) return Result.Ok($"({valueTuple.Item1},{valueTuple.Item2})");
        if (obj is ValueTuple<long, long> valueTuple2) return Result.Ok($"({valueTuple2.Item1},{valueTuple2.Item2})");
        var typeDesc = type.GetClassGenericFullName();
        if (typeDesc.Name.StartsWith("System.ValueTuple<"))
        {
            var li = new List<string>();
            var accessor = Accessor.Build(type);
            for (int i = 0; i < typeDesc.GenericTypes.Count; i++)
            {
                li.Add(ConvertToSqlSeg(accessor[obj, "Item" + (i + 1)]).UnWrap());
            }
            return Result.Ok($"({li.ToStringSeparated(",")})");
        }
        #endregion

        return Result.NotOk($"未能转换 {obj.GetType().FullName} 类型数据到sql!");
    }

    /// <summary>
    /// 将表达式解析为某一种JsonDataType,与 <seealso cref="ConvertJsonVariableToSql"/> 配合使用
    /// </summary>
    public virtual EnumJsonDataType ParseJsonDataType(Expression exp)
    {
        if (exp.Type == typeof(object) && exp.NodeType == ExpressionType.Convert)
            return ParseJsonDataType((exp as UnaryExpression).Operand);
        return ParseJsonDataType(exp.Type);
    }

    /// <summary>
    /// 将 <c>Type</c> 解析为某一种JsonDataType,与 <seealso cref="ConvertJsonVariableToSql"/> 配合使用
    /// </summary>
    public virtual EnumJsonDataType ParseJsonDataType(Type type)
    {
        if (type.IsNullable()) type = Nullable.GetUnderlyingType(type);
        if (type.IsAssignableTo(typeof(string))) return EnumJsonDataType.String;

        if (type.IsAssignableTo(typeof(IEnumerable)))
        {
            if (type == typeof(JsonObject)) return EnumJsonDataType.Object;
            var reflect = type.GetClassGenericFullName();
            if (reflect.Name == "Newtonsoft.Json.Linq.JObject"
                || reflect.Name == "System.Collections.Generic.Dictionary<TKey, TValue>"
                || reflect.Name == "System.Collections.Generic.IDictionary<TKey, TValue>"
                )
                return EnumJsonDataType.Object;
            return EnumJsonDataType.Array;
        }
        if (type.IsNumeric() || type.IsEnum) return EnumJsonDataType.Number;
        if (type == typeof(bool)) return EnumJsonDataType.Bool;
        //typeof(MySqlConnector.MySqlDateTime)//不考虑 MySqlDateTime.ToJson() 形式为 {"":""...}
        if (type.IsAssignableTo(typeof(string))
            || type == typeof(Guid)
            || type == typeof(DateTime)
            || type == typeof(DateTime)
            || type == typeof(DateTimeOffset)
            || type == typeof(DateOnly)
            || type == typeof(TimeOnly)
            || type == typeof(TimeSpan)
            || type.GetTypeCode() == TypeCode.DateTime
            ) return EnumJsonDataType.String;
        return EnumJsonDataType.Object;
    }

    /// <summary>
    /// 不同的位置,需要不同的写法,比如:
    /// <list type="number">
    /// <item>[JsonStore(Bucket="properties")] public string Name{ get; set; }
    /// <list type="bullet">
    ///     <item>set properties = 'tom' //err</item>
    ///     <item>set properties = '"tom"' //success</item>
    ///     <item>set properties = concat('a','b') //err</item>
    ///     <item>set properties = json_quote(concat('a','b')) success</item>
    /// </list>
    /// </item>
    /// <item>[JsonStore(Bucket="properties")] public bool Flag{ get; set; }
    /// <list type="bullet">
    /// <item>set properties = true //err</item>
    /// <item>set properties = 'true' //success</item>
    /// <item>set properties = 1>0 //err</item>
    /// <item>set properties = cast(1>0 as json) success</item>
    /// </list>
    /// </item>
    /// <item>想创建一个数组,里面只有对象 {"age":18}
    /// <list type="bullet">
    /// <item>select json_array('{"age":18}') //err 创建的数组里面是字符串 ["{\"age\":18}"]</item>
    /// <item>select json_array(cast('{"age":18}' as json)) //success [{"age": 18}]</item>
    /// </list>
    /// </item>
    /// <item>所以,将json分为 JsonDoc 和 JsonValue
    /// <list type="bullet">
    /// <item>set properties = 接受的是 JsonDoc</item>
    /// <item>json_array 接受 JsonValue,返回 JsonDoc</item>
    /// <item>json_merge_patch 接受的是 JsonDoc 返回的是 JsonDoc</item>
    /// <item>json_extract 接受的是 JsonDoc 返回的是 JsonDoc</item>
    /// <item>json_value 接受的是 JsonDoc 返回的是 JsonValue</item>
    /// </list>
    /// </item>
    /// </list>
    /// </summary>
    /// <param name="inst">常量</param>
    /// <param name="destType">目标位置</param>
    public virtual (string res, EnumJsonDataType type) ConvertJsonLiteralToSql(object inst, EnumJsonAcceptAsType destType)
    {
        //不能 ignorenull, 因为不知道用到哪里, 如果是 json_merge_patch 还指望 null 值去将原来的擦除呢
        //如:
        //select json_merge_patch('{"detail":{"age":20}}','{"name":"jack","detail":null}')  //out: {"name": "jack"}
        //select json_merge_patch('{"detail":{"age":20}}','{"name":"jack"}')  //out: {"name": "jack", "detail": {"age": 20}}
        var json = inst.ToJsonFast(ignoreJsonPropertyName: true);
        if (json == null) return ("null", EnumJsonDataType.Null);
        if (json.StartsWith('{') || json.StartsWith('['))
        {
            //object or array
            /* 假设: obj = new { name = 
             * """
             * "
             * '
             * """}
             * ToJson 的返回结果如: {"name":"\"\r\n'"} 此时,它还不能参与sql拼接,如:
             * select cast('{"name":"\"\r\n'"}' as json) -- 语法直接报错
             * select cast('{"name":"\"\r\n"}' as json) -- 去掉单引号后执行报错
             * 正确的应该是: select cast('{\"name\":\"\\\"\\r\\n\'\"}' as json)
             * 即, 将 ToJson() 的返回再进行一次转义
             */
            var dataType = json.StartsWith('{') ? EnumJsonDataType.Object : EnumJsonDataType.Array;
            if (destType == EnumJsonAcceptAsType.Value)
                //return cast('[1,2]' as json)
                return ($"cast({ProtectStringToSeg(json)} as json)", dataType);
            else if (destType == EnumJsonAcceptAsType.Doc)
                //return '[1,2]'
                return (ProtectStringToSeg(json), dataType);
            else
                return (json, dataType);
        }
        else if (json.StartsWith('\"'))
        {
            // string
            // ToJson 的返回结果如: "name", "na\"me'" 此时,它还不能参与sql拼接,如:
            //string json = "na\"me" => json_array('na\"me') ...
            if (destType == EnumJsonAcceptAsType.Value)
                //return 'tom'
                return ('\'' + json.Substring(1, json.Length - 2).Replace("'", "\\'") + '\'', EnumJsonDataType.String);
            else if (destType == EnumJsonAcceptAsType.Doc)
                //return '"tom"'
                return (ProtectStringToSeg(json), EnumJsonDataType.String);
            else
                return (json, EnumJsonDataType.String);
        }
        else if (json == "true" || json == "false")
        {
            //bool
            if (destType == EnumJsonAcceptAsType.Value)
                //return true
                return (json, EnumJsonDataType.Bool);
            else if (destType == EnumJsonAcceptAsType.Doc)
                //return 'true'
                return ($"'{json}'", EnumJsonDataType.Bool);
            else
                return (json, EnumJsonDataType.Bool);
        }
        else
        {
            //number
            if (destType == EnumJsonAcceptAsType.Doc)
                //return cast(1 as json)
                return ($"cast({json} as json)", EnumJsonDataType.Number);
            else if (destType == EnumJsonAcceptAsType.Value)
                //return 1
                return (json, EnumJsonDataType.Number);
            else
                return (json, EnumJsonDataType.Number);
        }
    }

    /// <summary>
    /// 不同的位置,需要不同的写法,比如:
    /// <list type="number">
    /// <item>[JsonStore(Bucket="properties")] public string Name{ get; set; }
    /// <list type="bullet">
    ///     <item>set properties = 'tom' //err</item>
    ///     <item>set properties = '"tom"' //success</item>
    ///     <item>set properties = concat('a','b') //err</item>
    ///     <item>set properties = json_quote(concat('a','b')) success</item>
    /// </list>
    /// </item>
    /// <item>[JsonStore(Bucket="properties")] public bool Flag{ get; set; }
    /// <list type="bullet">
    /// <item>set properties = true //err</item>
    /// <item>set properties = 'true' //success</item>
    /// <item>set properties = 1>0 //err</item>
    /// <item>set properties = cast(1>0 as json) success</item>
    /// </list>
    /// </item>
    /// <item>想创建一个数组,里面只有对象 {"age":18}
    /// <list type="bullet">
    /// <item>select json_array('{"age":18}') //err 创建的数组里面是字符串 ["{\"age\":18}"]</item>
    /// <item>select json_array(cast('{"age":18}' as json)) //success [{"age": 18}]</item>
    /// </list>
    /// </item>
    /// <item>所以,将json分为 JsonDoc 和 JsonValue
    /// <list type="bullet">
    /// <item>set properties = 接受的是 JsonDoc</item>
    /// <item>json_array 接受 JsonValue,返回 JsonDoc</item>
    /// <item>json_merge_patch 接受的是 JsonDoc 返回的是 JsonDoc</item>
    /// <item>json_extract 接受的是 JsonDoc 返回的是 JsonDoc</item>
    /// <item>json_value 接受的是 JsonDoc 返回的是 JsonValue</item>
    /// </list>
    /// </item>
    /// </list>
    /// </summary>
    /// <param name="seg">sqlseg</param>
    /// <param name="srcType">原json类型</param>
    /// <param name="destType">目标位置</param>
    public abstract string ConvertJsonVariableToSql(string seg, EnumJsonDataType srcType, EnumJsonAcceptAsType destType);
    #endregion
}
